#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
SQL编译器综合测试套件
测试大量不同类型的SQL语句，验证编译器的完整性和健壮性

测试覆盖：
1. 基本SQL语句（CREATE, INSERT, SELECT, UPDATE, DELETE）
2. 数据库管理（CREATE/DROP DATABASE, USE, LIST/SHOW DATABASES/SCHEMAS）
3. 用户管理（CREATE/DROP USER, LIST/SHOW USERS）
4. 权限管理（GRANT语句，各种权限类型和对象）
5. 高级SELECT功能（LIMIT, LIKE, HAVING）
6. 聚合函数（COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIANCE）
7. 逻辑操作符（AND, OR, 括号表达式）
8. UNION操作（UNION, UNION ALL）
9. 复杂查询（JOIN, ORDER BY, GROUP BY）
10. 表别名和qualified identifiers
11. 各种数据类型和操作符
12. 错误处理和边界情况
"""

import sys
import time
from typing import List, Dict, Any
from LexicalAnalysis import LexicalAnalyzer
from SyntaxAnalysis import SyntaxAnalyzer
from SemanticAnalysis import SemanticAnalyzer
from CodeGeneration import CodeGenerator


class SQLTestSuite:
    """SQL测试套件"""
    
    def __init__(self):
        self.lexical_analyzer = LexicalAnalyzer()
        self.syntax_analyzer = SyntaxAnalyzer()
        self.semantic_analyzer = SemanticAnalyzer()
        self.code_generator = CodeGenerator()
        
        # 测试统计
        self.total_tests = 0
        self.passed_tests = 0
        self.failed_tests = 0
        self.test_results = []
    
    def run_all_tests(self):
        """运行所有测试"""
        print("🚀 SQL编译器综合测试套件")
        print("=" * 80)
        
        start_time = time.time()
        
        # 运行各类测试
        self._test_basic_statements()
        self._test_create_statements()
        self._test_database_management()
        self._test_user_management()
        self._test_privilege_management()
        self._test_insert_statements()
        self._test_select_statements()
        self._test_advanced_select()
        self._test_aggregate_functions()
        self._test_logical_operators()
        self._test_union_operations()
        self._test_update_statements()
        self._test_delete_statements()
        self._test_join_operations()
        self._test_order_group_clauses()
        self._test_table_aliases()
        self._test_complex_queries()
        self._test_error_cases()
        self._test_edge_cases()
        
        end_time = time.time()
        
        # 打印测试总结
        self._print_test_summary(end_time - start_time)
    
    def _test_basic_statements(self):
        """测试基本SQL语句"""
        print("\n📋 测试基本SQL语句")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "简单SELECT",
                "sql": "SELECT name FROM Students;",
                "expected": "通过"
            },
            {
                "name": "SELECT所有列",
                "sql": "SELECT * FROM Students;",
                "expected": "通过"
            },
            {
                "name": "多列SELECT",
                "sql": "SELECT name, age, id FROM Students;",
                "expected": "通过"
            },
            {
                "name": "带WHERE的SELECT",
                "sql": "SELECT name FROM Students WHERE age > 20;",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("基本语句", test_cases)
    
    def _test_create_statements(self):
        """测试CREATE语句"""
        print("\n🏗️  测试CREATE语句")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "创建简单表",
                "sql": "CREATE TABLE student(id INT, name VARCHAR);",
                "expected": "通过"
            },
            {
                "name": "创建多列表",
                "sql": "CREATE TABLE employee(id INT, name VARCHAR, age INT, salary INT);",
                "expected": "通过"
            },
            {
                "name": "创建不同数据类型表",
                "sql": "CREATE TABLE product(id INT, name VARCHAR, price INT, description VARCHAR);",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("CREATE语句", test_cases)
    
    def _test_database_management(self):
        """测试数据库管理语句"""
        print("\n🗄️  测试数据库管理语句")
        print("-" * 40)
        
        # 正常测试用例
        normal_cases = [
            {
                "name": "创建数据库",
                "sql": "CREATE DATABASE new_test_db;",
                "expected": "通过"
            },
            {
                "name": "删除数据库",
                "sql": "DROP DATABASE test_db;",
                "expected": "通过"
            },
            {
                "name": "使用数据库",
                "sql": "USE hr_system;",
                "expected": "通过"
            },
            {
                "name": "列出数据库",
                "sql": "LIST DATABASES;",
                "expected": "通过"
            },
            {
                "name": "显示数据库",
                "sql": "SHOW DATABASES;",
                "expected": "通过"
            },
            {
                "name": "列出模式",
                "sql": "LIST SCHEMAS;",
                "expected": "通过"
            },
            {
                "name": "显示模式",
                "sql": "SHOW SCHEMAS;",
                "expected": "通过"
            }
        ]
        
        # 错误测试用例
        error_cases = [
            {
                "name": "使用不存在的数据库",
                "sql": "USE unknown_database;",
                "expected": "语义错误"
            }
        ]
        
        self._run_test_group("数据库管理-正常", normal_cases, expect_errors=False)
        self._run_test_group("数据库管理-错误", error_cases, expect_errors=True)
    
    def _test_user_management(self):
        """测试用户管理语句"""
        print("\n👤 测试用户管理语句")
        print("-" * 40)
        
        # 正常测试用例
        normal_cases = [
            {
                "name": "创建用户（无密码）",
                "sql": "CREATE USER new_user_123;",
                "expected": "通过"
            },
            {
                "name": "创建用户（有密码）",
                "sql": "CREATE USER test_user_456 IDENTIFIED BY 'password123';",
                "expected": "通过"
            },
            {
                "name": "删除用户",
                "sql": "DROP USER old_user;",
                "expected": "通过"
            },
            {
                "name": "列出用户",
                "sql": "LIST USERS;",
                "expected": "通过"
            },
            {
                "name": "显示用户",
                "sql": "SHOW USERS;",
                "expected": "通过"
            }
        ]
        
        # 错误测试用例
        error_cases = [
            {
                "name": "创建已存在用户",
                "sql": "CREATE USER root;",
                "expected": "语义错误"
            },
            {
                "name": "删除不存在用户",
                "sql": "DROP USER nonexistent_user;",
                "expected": "语义错误"
            }
        ]
        
        self._run_test_group("用户管理-正常", normal_cases, expect_errors=False)
        self._run_test_group("用户管理-错误", error_cases, expect_errors=True)
    
    def _test_privilege_management(self):
        """测试权限管理语句"""
        print("\n🔐 测试权限管理语句")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "基本GRANT语句",
                "sql": "GRANT SELECT ON Students TO alice;",
                "expected": "通过"
            },
            {
                "name": "多权限GRANT",
                "sql": "GRANT SELECT, INSERT ON TABLE Students TO alice, bob;",
                "expected": "通过"
            },
            {
                "name": "数据库级权限",
                "sql": "GRANT SELECT ON DATABASE hr_system TO USER alice;",
                "expected": "通过"
            },
            {
                "name": "角色权限",
                "sql": "GRANT UPDATE ON Students TO ROLE admin_role;",
                "expected": "通过"
            },
            {
                "name": "带GRANT OPTION",
                "sql": "GRANT SELECT ON Students TO alice WITH GRANT OPTION;",
                "expected": "通过"
            },
            {
                "name": "ALL权限",
                "sql": "GRANT ALL PRIVILEGES ON DATABASE hr_system TO USER manager;",
                "expected": "通过"
            },
            {
                "name": "PUBLIC权限",
                "sql": "GRANT SELECT ON Students TO PUBLIC;",
                "expected": "通过"
            },
            {
                "name": "不存在表的权限",
                "sql": "GRANT SELECT ON UnknownTable TO alice;",
                "expected": "语义错误"
            }
        ]
        
        self._run_test_group("权限管理", test_cases)
    
    def _test_insert_statements(self):
        """测试INSERT语句"""
        print("\n📥 测试INSERT语句")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "插入单行数据",
                "sql": "INSERT INTO Students (name, age, id) VALUES ('Alice', 20, 1);",
                "expected": "通过"
            },
            {
                "name": "插入字符串数据",
                "sql": "INSERT INTO Students (name, major) VALUES ('Bob', 'Computer Science');",
                "expected": "通过"
            },
            {
                "name": "插入数字数据",
                "sql": "INSERT INTO Students (id, age) VALUES (123, 25);",
                "expected": "通过"
            },
            {
                "name": "插入混合数据",
                "sql": "INSERT INTO Students (name, age, id, major) VALUES ('Charlie', 22, 456, 'Mathematics');",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("INSERT语句", test_cases)
    
    def _test_select_statements(self):
        """测试SELECT语句"""
        print("\n🔍 测试SELECT语句")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "基本SELECT",
                "sql": "SELECT name FROM Students;",
                "expected": "通过"
            },
            {
                "name": "多表SELECT",
                "sql": "SELECT name FROM Teachers;",
                "expected": "通过"
            },
            {
                "name": "条件SELECT",
                "sql": "SELECT name, age FROM Students WHERE age >= 18;",
                "expected": "通过"
            },
            {
                "name": "不等于条件",
                "sql": "SELECT name FROM Students WHERE id != 0;",
                "expected": "通过"
            },
            {
                "name": "小于等于条件",
                "sql": "SELECT name FROM Students WHERE age <= 25;",
                "expected": "通过"
            },
            {
                "name": "大于条件",
                "sql": "SELECT name FROM Students WHERE age > 18;",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("SELECT语句", test_cases)
    
    def _test_advanced_select(self):
        """测试高级SELECT功能"""
        print("\n🔍+ 测试高级SELECT功能")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "LIMIT子句",
                "sql": "SELECT name FROM Students LIMIT 5;",
                "expected": "通过"
            },
            {
                "name": "LIKE操作符",
                "sql": "SELECT name FROM Students WHERE name LIKE 'John%';",
                "expected": "通过"
            },
            {
                "name": "HAVING子句",
                "sql": "SELECT major FROM Students GROUP BY major HAVING COUNT(*) > 5;",
                "expected": "通过"
            },
            {
                "name": "复杂LIKE模式",
                "sql": "SELECT name FROM Students WHERE name LIKE '_ohn';",
                "expected": "通过"
            },
            {
                "name": "LIMIT与WHERE结合",
                "sql": "SELECT name FROM Students WHERE age > 18 LIMIT 10;",
                "expected": "通过"
            },
            {
                "name": "ORDER BY与LIMIT结合",
                "sql": "SELECT name FROM Students ORDER BY age DESC LIMIT 3;",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("高级SELECT", test_cases)
    
    def _test_aggregate_functions(self):
        """测试聚合函数"""
        print("\n📊 测试聚合函数")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "COUNT(*)",
                "sql": "SELECT COUNT(*) FROM Students;",
                "expected": "通过"
            },
            {
                "name": "COUNT(列名)",
                "sql": "SELECT COUNT(name) FROM Students;",
                "expected": "通过"
            },
            {
                "name": "SUM函数",
                "sql": "SELECT SUM(age) FROM Students;",
                "expected": "通过"
            },
            {
                "name": "AVG函数",
                "sql": "SELECT AVG(age) FROM Students;",
                "expected": "通过"
            },
            {
                "name": "MAX函数",
                "sql": "SELECT MAX(age) FROM Students;",
                "expected": "通过"
            },
            {
                "name": "MIN函数",
                "sql": "SELECT MIN(age) FROM Students;",
                "expected": "通过"
            },
            {
                "name": "多个聚合函数",
                "sql": "SELECT COUNT(*), AVG(age), MAX(age), MIN(age) FROM Students;",
                "expected": "通过"
            },
            {
                "name": "聚合函数与WHERE",
                "sql": "SELECT COUNT(*) FROM Students WHERE age > 18;",
                "expected": "通过"
            },
            {
                "name": "聚合函数与GROUP BY",
                "sql": "SELECT major, COUNT(*) FROM Students GROUP BY major;",
                "expected": "通过"
            },
            {
                "name": "STDDEV函数",
                "sql": "SELECT STDDEV(age) FROM Students;",
                "expected": "通过"
            },
            {
                "name": "VARIANCE函数",
                "sql": "SELECT VARIANCE(age) FROM Students;",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("聚合函数", test_cases)
    
    def _test_logical_operators(self):
        """测试逻辑操作符"""
        print("\n🔗 测试逻辑操作符")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "简单AND条件",
                "sql": "SELECT name FROM Students WHERE age > 18 AND major = 'CS';",
                "expected": "通过"
            },
            {
                "name": "简单OR条件",
                "sql": "SELECT name FROM Students WHERE age < 20 OR age > 25;",
                "expected": "通过"
            },
            {
                "name": "多个AND条件",
                "sql": "SELECT name FROM Students WHERE age > 18 AND age < 25 AND major = 'CS';",
                "expected": "通过"
            },
            {
                "name": "多个OR条件",
                "sql": "SELECT name FROM Students WHERE major = 'CS' OR major = 'Math' OR major = 'Physics';",
                "expected": "通过"
            },
            {
                "name": "括号表达式",
                "sql": "SELECT name FROM Students WHERE (age > 18 AND age < 25) OR major = 'CS';",
                "expected": "通过"
            },
            {
                "name": "混合AND/OR",
                "sql": "SELECT name FROM Students WHERE age > 20 AND (major = 'CS' OR major = 'Math');",
                "expected": "通过"
            },
            {
                "name": "LIKE与AND结合",
                "sql": "SELECT name FROM Students WHERE name LIKE 'A%' AND age > 18;",
                "expected": "通过"
            },
            {
                "name": "LIKE与OR结合",
                "sql": "SELECT name FROM Students WHERE name LIKE 'John%' OR name LIKE 'Jane%';",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("逻辑操作符", test_cases)
    
    def _test_union_operations(self):
        """测试UNION操作"""
        print("\n🔄 测试UNION操作")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "基本UNION",
                "sql": "SELECT name FROM Students UNION SELECT name FROM Teachers;",
                "expected": "通过"
            },
            {
                "name": "UNION ALL",
                "sql": "SELECT id, name FROM Students UNION ALL SELECT id, name FROM Teachers;",
                "expected": "通过"
            },
            {
                "name": "带WHERE的UNION",
                "sql": "SELECT name FROM Students WHERE age > 20 UNION SELECT name FROM Teachers WHERE age < 60;",
                "expected": "通过"
            },
            {
                "name": "UNION与ORDER BY",
                "sql": "SELECT name FROM Students UNION SELECT name FROM Teachers ORDER BY name;",
                "expected": "通过"
            },
            {
                "name": "多个UNION",
                "sql": "SELECT name FROM Students UNION SELECT name FROM Teachers UNION SELECT name FROM Courses;",
                "expected": "通过"
            },
            {
                "name": "复杂UNION查询",
                "sql": "SELECT name, age FROM Students WHERE age > 18 UNION ALL SELECT name, age FROM Teachers WHERE age < 65 ORDER BY age;",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("UNION操作", test_cases)
    
    def _test_update_statements(self):
        """测试UPDATE语句"""
        print("\n✏️  测试UPDATE语句")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "简单UPDATE",
                "sql": "UPDATE Students SET age = 21;",
                "expected": "通过"
            },
            {
                "name": "带WHERE的UPDATE",
                "sql": "UPDATE Students SET age = 22 WHERE id = 1;",
                "expected": "通过"
            },
            {
                "name": "UPDATE字符串",
                "sql": "UPDATE Students SET name = 'David' WHERE id = 2;",
                "expected": "通过"
            },
            {
                "name": "多列UPDATE",
                "sql": "UPDATE Students SET name = 'Eve', age = 23;",
                "expected": "通过"
            },
            {
                "name": "复杂条件UPDATE",
                "sql": "UPDATE Students SET age = 24 WHERE age > 20;",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("UPDATE语句", test_cases)
    
    def _test_delete_statements(self):
        """测试DELETE语句"""
        print("\n🗑️  测试DELETE语句")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "带条件DELETE",
                "sql": "DELETE FROM Students WHERE id = 1;",
                "expected": "通过"
            },
            {
                "name": "复杂条件DELETE",
                "sql": "DELETE FROM Students WHERE age >= 25;",
                "expected": "通过"
            },
            {
                "name": "不等于条件DELETE",
                "sql": "DELETE FROM Students WHERE name != 'Alice';",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("DELETE语句", test_cases)
    
    def _test_join_operations(self):
        """测试JOIN操作"""
        print("\n🔗 测试JOIN操作")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "INNER JOIN",
                "sql": "SELECT s.name FROM Students s INNER JOIN Teachers t ON s.id = t.id;",
                "expected": "通过"
            },
            {
                "name": "LEFT JOIN",
                "sql": "SELECT s.name, t.name FROM Students s LEFT JOIN Teachers t ON s.id = t.id;",
                "expected": "通过"
            },
            {
                "name": "RIGHT JOIN",
                "sql": "SELECT s.name FROM Students s RIGHT JOIN Teachers t ON s.id = t.id;",
                "expected": "通过"
            },
            {
                "name": "多表JOIN",
                "sql": "SELECT s.name FROM Students s INNER JOIN Courses c ON s.id = c.id;",
                "expected": "通过"
            },
            {
                "name": "复杂JOIN条件",
                "sql": "SELECT s.name, t.department FROM Students s INNER JOIN Teachers t ON s.id = t.id;",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("JOIN操作", test_cases)
    
    def _test_order_group_clauses(self):
        """测试ORDER BY和GROUP BY子句"""
        print("\n📊 测试ORDER BY和GROUP BY子句")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "ORDER BY ASC",
                "sql": "SELECT name FROM Students ORDER BY age ASC;",
                "expected": "通过"
            },
            {
                "name": "ORDER BY DESC",
                "sql": "SELECT name FROM Students ORDER BY name DESC;",
                "expected": "通过"
            },
            {
                "name": "ORDER BY默认",
                "sql": "SELECT name FROM Students ORDER BY id;",
                "expected": "通过"
            },
            {
                "name": "GROUP BY单列",
                "sql": "SELECT major FROM Students GROUP BY major;",
                "expected": "通过"
            },
            {
                "name": "GROUP BY多列",
                "sql": "SELECT major, age FROM Students GROUP BY major;",
                "expected": "通过"
            },
            {
                "name": "ORDER BY + GROUP BY",
                "sql": "SELECT major FROM Students GROUP BY major ORDER BY major ASC;",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("ORDER BY和GROUP BY", test_cases)
    
    def _test_table_aliases(self):
        """测试表别名"""
        print("\n🏷️  测试表别名")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "简单表别名",
                "sql": "SELECT s.name FROM Students s;",
                "expected": "通过"
            },
            {
                "name": "多列表别名",
                "sql": "SELECT s.name, s.age FROM Students s;",
                "expected": "通过"
            },
            {
                "name": "表别名WHERE",
                "sql": "SELECT s.name FROM Students s WHERE s.age > 20;",
                "expected": "通过"
            },
            {
                "name": "表别名ORDER BY",
                "sql": "SELECT s.name FROM Students s ORDER BY s.age;",
                "expected": "通过"
            },
            {
                "name": "表别名GROUP BY",
                "sql": "SELECT s.major FROM Students s GROUP BY s.major;",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("表别名", test_cases)
    
    def _test_complex_queries(self):
        """测试复杂查询"""
        print("\n🎯 测试复杂查询")
        print("-" * 40)
        
        test_cases = [
            {
                "name": "复杂SELECT with JOIN",
                "sql": "SELECT s.name, t.name FROM Students s INNER JOIN Teachers t ON s.id = t.id WHERE s.age > 20;",
                "expected": "通过"
            },
            {
                "name": "JOIN + ORDER BY",
                "sql": "SELECT s.name FROM Students s INNER JOIN Teachers t ON s.id = t.id ORDER BY s.name ASC;",
                "expected": "通过"
            },
            {
                "name": "JOIN + GROUP BY",
                "sql": "SELECT s.major FROM Students s INNER JOIN Teachers t ON s.id = t.id GROUP BY s.major;",
                "expected": "通过"
            },
            {
                "name": "全功能查询",
                "sql": "SELECT s.name FROM Students s INNER JOIN Teachers t ON s.id = t.id WHERE s.age > 18 ORDER BY s.name ASC;",
                "expected": "通过"
            },
            {
                "name": "多表复杂查询",
                "sql": "SELECT s.name, c.name FROM Students s LEFT JOIN Courses c ON s.id = c.teacher_id WHERE s.age >= 20 GROUP BY s.major ORDER BY s.name DESC;",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("复杂查询", test_cases)
    
    def _test_error_cases(self):
        """测试错误情况"""
        print("\n❌ 测试错误情况")
        print("-" * 40)
        
        test_cases = [
            # 基本错误
            {
                "name": "未知表",
                "sql": "SELECT name FROM UnknownTable;",
                "expected": "语义错误"
            },
            {
                "name": "未知列",
                "sql": "SELECT unknown_column FROM Students;",
                "expected": "语义错误"
            },
            {
                "name": "语法错误-缺少FROM",
                "sql": "SELECT name Students;",
                "expected": "语法错误"
            },
            {
                "name": "语法错误-缺少分号",
                "sql": "SELECT name FROM Students",
                "expected": "语法错误"
            },
            {
                "name": "语法错误-空SELECT",
                "sql": "SELECT FROM Students;",
                "expected": "语法错误"
            },
            
            {
                "name": "删除不存在数据库",
                "sql": "DROP DATABASE nonexistent_db;",
                "expected": "语义错误"
            },
            
            # 聚合函数错误
            {
                "name": "聚合函数语法错误",
                "sql": "SELECT COUNT FROM Students;",
                "expected": "语法错误"
            },
            {
                "name": "无效聚合函数参数",
                "sql": "SELECT SUM(*) FROM Students;",
                "expected": "语义错误"
            },
            
            # 逻辑操作符错误
            {
                "name": "AND后缺少条件",
                "sql": "SELECT name FROM Students WHERE age > 18 AND;",
                "expected": "语法错误"
            },
            {
                "name": "OR前缺少条件",
                "sql": "SELECT name FROM Students WHERE OR age < 25;",
                "expected": "语法错误"
            },
            
            # LIMIT错误
            {
                "name": "LIMIT无效值",
                "sql": "SELECT name FROM Students LIMIT 0;",
                "expected": "语义错误"
            },
            {
                "name": "LIMIT非数字",
                "sql": "SELECT name FROM Students LIMIT 'abc';",
                "expected": "语法错误"
            },
            
            # GRANT错误
            {
                "name": "GRANT语法错误",
                "sql": "GRANT SELECT Students TO alice;",
                "expected": "语法错误"
            },
            {
                "name": "GRANT未知权限",
                "sql": "GRANT UNKNOWN_PRIVILEGE ON Students TO alice;",
                "expected": "语法错误"
            }
        ]
        
        self._run_test_group("错误情况", test_cases, expect_errors=True)
    
    def _test_edge_cases(self):
        """测试边界情况"""
        print("\n🔄 测试边界情况")
        print("-" * 40)
        
        test_cases = [
            # 基本边界情况
            {
                "name": "单字符表名",
                "sql": "SELECT name FROM s;",
                "expected": "语义错误"  # 表s不存在
            },
            {
                "name": "长标识符",
                "sql": "SELECT very_long_column_name FROM Students;",
                "expected": "语义错误"  # 列不存在
            },
            {
                "name": "数字常量",
                "sql": "SELECT name FROM Students WHERE age = 123;",
                "expected": "通过"
            },
            {
                "name": "字符串常量",
                "sql": "SELECT name FROM Students WHERE name = 'TestName';",
                "expected": "通过"
            },
            
            # 新功能边界情况
            {
                "name": "LIMIT为0",
                "sql": "SELECT name FROM Students LIMIT 0;",
                "expected": "语义错误"
            },
            {
                "name": "LIMIT最大值",
                "sql": "SELECT name FROM Students LIMIT 999999;",
                "expected": "通过"
            },
            {
                "name": "空字符串LIKE",
                "sql": "SELECT name FROM Students WHERE name LIKE '';",
                "expected": "通过"
            },
            {
                "name": "复杂嵌套括号",
                "sql": "SELECT name FROM Students WHERE ((age > 18 AND age < 25) OR (age > 60 AND age < 70));",
                "expected": "通过"
            },
            {
                "name": "聚合函数在WHERE中",
                "sql": "SELECT name FROM Students WHERE COUNT(*) > 5;",
                "expected": "通过"  # 当前实现允许这种用法
            },
            {
                "name": "多层UNION",
                "sql": "SELECT name FROM Students UNION SELECT name FROM Teachers UNION ALL SELECT name FROM Courses;",
                "expected": "通过"
            },
            {
                "name": "用户名包含特殊字符",
                "sql": "CREATE USER test_user_123;",
                "expected": "通过"
            },
            {
                "name": "长密码字符串",
                "sql": "CREATE USER test_user_789 IDENTIFIED BY 'very_long_password_with_special_characters_123!@#';",
                "expected": "通过"
            }
        ]
        
        self._run_test_group("边界情况", test_cases, expect_errors=True)
    
    def _run_test_group(self, group_name: str, test_cases: List[Dict[str, str]], expect_errors: bool = False):
        """运行测试组"""
        group_passed = 0
        group_total = len(test_cases)
        
        for test_case in test_cases:
            self.total_tests += 1
            result = self._run_single_test(test_case, expect_errors)
            
            if result["passed"]:
                group_passed += 1
                self.passed_tests += 1
                print(f"  ✅ {test_case['name']}")
            else:
                self.failed_tests += 1
                print(f"  ❌ {test_case['name']}: {result['error']}")
            
            self.test_results.append(result)
        
        print(f"  📈 {group_name}: {group_passed}/{group_total} 通过")
    
    def _run_single_test(self, test_case: Dict[str, str], expect_errors: bool = False) -> Dict[str, Any]:
        """运行单个测试"""
        try:
            sql = test_case["sql"]
            
            # 词法分析
            tokens = self.lexical_analyzer.analyze(sql)
            
            # 语法分析
            if self.total_tests == 1:  # 第一次需要构建分析表
                syntax_result = self.syntax_analyzer.analyze(tokens)
            else:
                syntax_result = self.syntax_analyzer.predictive_parse(tokens)
            
            if not syntax_result:
                if expect_errors and "语法错误" in test_case.get("expected", ""):
                    return {"passed": True, "error": None, "test_case": test_case}
                else:
                    return {"passed": False, "error": "语法分析失败", "test_case": test_case}
            
            # 语义分析
            quadruples, symbol_table, semantic_errors = self.semantic_analyzer.analyze(tokens)
            
            if semantic_errors:
                if expect_errors and "语义错误" in test_case.get("expected", ""):
                    return {"passed": True, "error": None, "test_case": test_case}
                else:
                    return {"passed": False, "error": f"语义错误: {semantic_errors[0]}", "test_case": test_case}
            
            # 目标代码生成
            target_code, code_errors = self.code_generator.generate_code(quadruples, symbol_table)
            
            if code_errors:
                return {"passed": False, "error": f"代码生成错误: {code_errors[0]}", "test_case": test_case}
            
            # 如果期望错误但没有错误，则测试失败
            if expect_errors and test_case.get("expected") not in ["通过"]:
                return {"passed": False, "error": "期望错误但测试通过", "test_case": test_case}
            
            return {"passed": True, "error": None, "test_case": test_case}
            
        except Exception as e:
            if expect_errors:
                return {"passed": True, "error": None, "test_case": test_case}
            else:
                return {"passed": False, "error": f"异常: {str(e)}", "test_case": test_case}
    
    def _print_test_summary(self, elapsed_time: float):
        """打印测试总结"""
        print("\n" + "=" * 80)
        print("🎯 测试总结")
        print("=" * 80)
        
        success_rate = (self.passed_tests / self.total_tests * 100) if self.total_tests > 0 else 0
        
        print(f"总测试数量: {self.total_tests}")
        print(f"通过测试: {self.passed_tests}")
        print(f"失败测试: {self.failed_tests}")
        print(f"成功率: {success_rate:.1f}%")
        print(f"执行时间: {elapsed_time:.2f}秒")
        
        # 打印失败的测试详情
        if self.failed_tests > 0:
            print(f"\n❌ 失败测试详情:")
            print("-" * 40)
            for result in self.test_results:
                if not result["passed"]:
                    test_case = result["test_case"]
                    print(f"  • {test_case['name']}")
                    print(f"    SQL: {test_case['sql']}")
                    print(f"    错误: {result['error']}")
                    print()
        
        # 总体评价
        if success_rate >= 90:
            print("🎉 优秀！编译器表现非常好！")
        elif success_rate >= 80:
            print("👍 良好！编译器基本功能正常！")
        elif success_rate >= 60:
            print("⚠️  一般！编译器需要改进！")
        else:
            print("🚨 需要大量改进！")


def main():
    """主函数"""
    print("SQL编译器综合测试套件")
    print("作者: AI Assistant")
    print("版本: 1.0")
    print()
    
    # 创建并运行测试套件
    test_suite = SQLTestSuite()
    test_suite.run_all_tests()


if __name__ == "__main__":
    main()
